* Medical School 

import excel "${raw_data}/tuition/AAMC Association of American Medical Colleges/tsf2012-2013through2018-2019.xlsx", sheet("Summary Statistics") cellrange(A7:J124) firstrow clear
keep if AcademicYear=="2016-2017"
keep if CostType=="Tuition, Fees, and Health Insurance" | CostType=="Health Insurance"
keep if (OwnershipType=="Public" & ResidenceStatus=="Resident") | (OwnershipType=="Private" & ResidenceStatus=="Nonresident")
keep CostType OwnershipType ResidenceStatus AverageCost
replace AverageCost=-1*AverageCost if CostType=="Health Insurance"
collapse (sum) AverageCost, by(OwnershipType ResidenceStatus)
gen AverageCost4Yrs=AverageCost*4
rename (AverageCost AverageCost4Yrs) (AverageCostMD AverageCost4YrsMD) 
keep OwnershipType AverageCostMD AverageCost4YrsMD
save "${temp}/medschool_cost_20162017.dta", replace

* Law degree 

import excel "${raw_data}/tuition/ABA American Bar Association - Law/Tuition fee expenses/2017_Tuition fee expenses.xlsx", sheet("2017_Tuitions_and_Fees_L") firstrow clear
keep schoollist FullTimeResident FullTimeNonresident
destring(FullTimeResident FullTimeNonresident), replace ignore(",")
replace FullTimeNonresident=FullTimeResident if FullTimeNonresident==0
replace FullTimeResident=FullTimeNonresident if FullTimeResident==0
gen diffResidentNonresident=FullTimeNonresident-FullTimeResident
gen private=(diffResidentNonresident<1000)
rename FullTimeResident AverageCost
gen OwnershipType="Public" if private==0	
replace OwnershipType="Private" if private==1
collapse (mean) AverageCost, by(OwnershipType)
gen AverageCost3Yrs=AverageCost*3
rename (AverageCost AverageCost3Yrs) (AverageCostJD AverageCost3YrsJD) 
keep OwnershipType AverageCostJD AverageCost3YrsJD
save "${temp}/lawschool_cost_20162017.dta", replace

* Undergraduate degree 

import excel "${raw_data}/tuition/NPSAS/Average undergradaute tuition and fees.xls", sheet("Digest 2018 Table 330.10") cellrange(A3:Y200) clear
keep A F 
gen AY20162017=(strpos(A,"2016-17"))
keep if AY20162017==1	
drop in 1	
drop AY20162017 A
gen OwnershipType="Public" if _n==1		
replace OwnershipType="Private" if _n==2
destring F, replace
rename F AverageCost	
gen AverageCost4Yrs=AverageCost*4
order OwnershipType 
rename (AverageCost AverageCost4Yrs) (AverageCostUG AverageCost4YrsUG) 
keep OwnershipType AverageCostUG AverageCost4YrsUG
save "${temp}/UG_cost_20162017.dta", replace
			
* Total cost of education for physicians and lawyers 

use "${temp}/medschool_cost_20162017.dta", clear
merge 1:1 OwnershipType using "${temp}/lawschool_cost_20162017.dta", nogen assert(match)
merge 1:1 OwnershipType using "${temp}/UG_cost_20162017.dta", nogen assert(match)
gen total_educ_cost_med=AverageCost4YrsMD+AverageCost4YrsUG
gen total_educ_cost_law=AverageCost3YrsJD+AverageCost4YrsUG
keep OwnershipType total_educ_cost_med total_educ_cost_law
export delimited "${mypath}/intermediate_csv/tuit01-total_educ_cost_2017.csv", replace
